package com.abel.bigwater.wflow.mapper

import com.abel.bigmeter.service.MeterParam
import com.abel.bigwater.model.BwDma
import com.abel.bigwater.model.BwMeterBrand
import com.abel.bigwater.model.BwRemoteBrand
import com.abel.bigwater.model.BwUser
import com.abel.bigwater.model.zone.ZoneMeter
import org.apache.ibatis.annotations.*

@Mapper
//@Component(value = "meterMapper")
interface MeterMapper {

    @Select("""
        SELECT meter_brand_id AS brand,
        meter_brand_name AS brandName
        FROM bw.bw_meter_brand
        ORDER BY meter_order
        """)
    fun selectMeterBrand(): List<BwMeterBrand>

    @Select("""
        SELECT remote_brand_id AS brand,
        remote_brand_name AS brandName
        FROM bw.bw_remote_brand
        ORDER BY remote_order
        """)
    fun selectRemoteBrand(): List<BwRemoteBrand>

    @Insert("""
        INSERT INTO bw.bw_meter (
        meter_id, user_code, meter_code,
        meter_order,
        ext_id,
        meter_name, meter_location, install_date,
        meter_pulse,
        q1, q2, q3, q4, q1r, q2r, q3r, q4r,
        meter_size, meter_model, meter_type_id, is_charge,
        in_out,
        create_by, create_date, update_by, update_date,
        dma_id, meter_brand_id, remote_brand_id,
        rtu_id,
        pressure_range,
        pressure_max_limit,
        pressure_min_limit,
        power_type,
        meter_status,
        admin_mobile,
        last_calib,
        firm_id
        , water_price
        , service_area
        , service_population
        , contact_number
        , rtu_code
        , rtu_addr
        , rtu_install_date
        , rtu_install_person
        , rtu_contact
        , comm_card
        , remote_model
        , remote_memo
        , comm_isp
        , meter_memo
        , meter_loc
        )
        values(
        #{id}, #{userCode}, #{meterCode},
        #{meterOrder},
        #{extId},
        #{name}, #{location}, #{installDate},
        #{meterPulse},
        #{q1}, #{q2}, #{q3}, #{q4}, #{q1r}, #{q2r}, #{q3r}, #{q4r},
        #{size}, #{model}, #{typeId}, #{chargable},
        #{inOutput},
        #{createBy}, #{createDate}, #{updateBy}, #{updateDate},
        #{dmaId}, #{meterBrandId}, #{remoteBrandId},
        #{rtuId}, #{pressureRange}, #{pressureMaxLimit}, #{pressureMinLimit},
        #{powerType}, #{meterStatus}, #{adminMobile}, #{lastCalib},
        #{firmId}
        , #{waterPrice}, #{serviceArea}, #{servicePopulation}, #{contactNumber}
        , #{rtuCode}, #{rtuAddr}, #{rtuInstallDate}, #{rtuInstallPerson}, #{rtuContact}
        , #{commCard}, #{remoteModel}, #{remoteMemo}, #{commIsp}
        , #{memo}
        , ST_GeomFromText(#{meterLoc})
        )
        """)
    @Options(useGeneratedKeys = false)
    fun insertMeter(meter: ZoneMeter): Int

    @Delete("""<script>
        DELETE FROM bw.bw_meter
        WHERE meter_id IN
        <foreach collection='midList' item='mid' index='idx' open='(' separator=',' close=')'>
            #{mid}
        </foreach>
        </script>""")
    fun deleteMeter(@Param("midList") midList: List<String>): Int

    @Update("""<script>
        UPDATE bw.bw_meter SET
        user_code = #{userCode},
        meter_code = #{meterCode},
        meter_name = #{name},
        meter_order = #{meterOrder},
        ext_id = #{extId},
        meter_location = #{location},
        install_date = #{installDate},
        meter_pulse = #{meterPulse},
        q1 = #{q1},
        q2 = #{q2},
        q3 = #{q3},
        q4 = #{q4},
        q1r = #{q1r},
        q2r = #{q2r},
        q3r = #{q3r},
        q4r = #{q4r},
        meter_size = #{size},
        meter_model = #{model},
        meter_type_id = #{typeId},
        is_charge = #{chargable},
        in_out = #{inOutput},
        create_by = #{createBy},
        create_date = #{createDate},
        update_by = #{updateBy},
        update_date = #{updateDate},
        dma_id = #{dmaId},
        meter_brand_id = #{meterBrandId},
        remote_brand_id = #{remoteBrandId},
        rtu_id = #{rtuId},
        pressure_range = #{pressureRange},
        pressure_max_limit = #{pressureMaxLimit},
        pressure_min_limit = #{pressureMinLimit},
        power_type = #{powerType},
        meter_status = #{meterStatus},
        admin_mobile = #{adminMobile},
        last_calib = #{lastCalib},
        firm_id = #{firmId}
        , water_price = #{waterPrice}
        , service_area = #{serviceArea}
        , service_population = #{servicePopulation}
        , contact_number = #{contactNumber}
        , rtu_code = #{rtuCode}
        , rtu_addr = #{rtuAddr}
        , rtu_install_date = #{rtuInstallDate}
        , rtu_install_person = #{rtuInstallPerson}
        , rtu_contact = #{rtuContact}
        , comm_card = #{commCard}
        , remote_model = #{remoteModel}
        , remote_memo = #{remoteMemo}
        , comm_isp = #{commIsp}
        , meter_memo = #{memo}
        <if test='meterLoc != null'>
            , meter_loc = ST_GeomFromText(#{meterLoc})
        </if>
        WHERE meter_id = #{id}
        </script>""")
    fun updateMeter(meter: ZoneMeter): Int

    @Update("""<script>
        UPDATE bw.bw_meter SET
            meter_loc = ST_GeomFromText(#{meterLoc})
        WHERE meter_id = #{id}
        </script>""")
    fun updateMeterLoc(meter: ZoneMeter): Int

    @Update("""
        <script>
        UPDATE bw.bw_meter
        <set>
            <if test="name != null">
                meter_name = #{name},
            </if>
            <if test="extId != null">
                ext_id = #{extId},
            </if>
            <if test="meterPulse != null">
                meter_pulse = #{meterPulse},
            </if>
            
            <if test="typeId != null">
                meter_type_id = #{typeId},
            </if>
            <if test="meterBrandId != null">
                meter_brand_id = #{meterBrandId},
            </if>
            <if test="userCode != null">
                user_code = #{userCode},
            </if>
            <if test="meterCode != null">
                meter_code = #{meterCode},
            </if>
            <if test="rtuCode != null">
                rtu_code = #{rtuCode},
            </if>
            <if test="size != null">
                meter_size = #{size},
            </if>
            <if test="commCard != null">
                comm_card = #{commCard},
            </if>
            <if test="installDate != null">
                install_date = #{installDate},
            </if>
            <if test="rtuInstallDate != null">
                rtu_install_date = #{rtuInstallDate},
            </if>
            <if test="meterLoc != null">
                meter_loc = ST_GeomFromText(#{meterLoc}),
            </if>

            <if test="updateBy != null">
                update_by = #{updateBy},
            </if>
            <if test="updateDate != null">
                update_date = #{updateDate},
            </if>

            <if test="dmaId != null">
                dma_id = #{dmaId},
            </if>
            <if test="firmId != null">
                firm_id = #{firmId}
            </if>
        </set>
        <where>
            <if test="id != null">
                meter_id = #{id}
            </if>
            <if test="id == null">
                ext_id = #{extId}
            </if>
        </where>
        </script>
    """)
    fun updateMeterByValue(meter: ZoneMeter): Int

    /**
     * <pre>
     * <parameterMap type="map" id="meterWhere">
     * <parameter property="id"></parameter>
     * <parameter property="name"></parameter>
     * <parameter property="dmaId"></parameter>
     * <parameter property="typeId"></parameter>
     * <parameter property="location"></parameter>
     * <parameter property="installDate1"></parameter>
     * <parameter property="installDate2"></parameter>
     * <parameter property="meterBrandId"></parameter>
     * <parameter property="remoteBrandId"></parameter>
     * <parameter property="firmId"></parameter>
    </parameterMap> *
     * typeId can be: CHECK/CHARGE/FIRE
    </pre> *
     *
     * @param map
     * @return
     */
    @Select("""<script>
        SELECT meter_id as id, user_code AS userCode,
        meter_code AS meterCode,
        meter_order AS meterOrder,
        ext_id AS extId,
        meter_name as name, meter_location as location, install_date as installDate,
        meter_pulse AS meterPulse,
        q1, q2, q3, q4, q1r, q2r, q3r, q4r,
        meter_size as size, meter_model as model, meter_type_id as typeId, is_charge as chargable,
        in_out AS inOutput,
        create_by as createBy, create_date as createDate, update_by as updateBy, update_date as updateDate,
        dma_id as dmaId,
        meter_brand_id as meterBrandId,
        remote_brand_id as remoteBrandId,
        rtu_id AS rtuId,
        pressure_range AS pressureRange,
        pressure_max_limit AS pressureMaxLimit,
        pressure_min_limit AS pressureMinLimit,
        power_type AS powerType,
        meter_status AS meterStatus,
        admin_mobile AS adminMobile,
        last_calib AS lastCalib,
        ST_AsText(meter_loc) AS meterLoc,
        firm_id AS firmId
        , water_price AS waterPrice
        , service_area AS serviceArea
        , service_population AS servicePopulation
        , contact_number AS contactNumber
        , rtu_code AS rtuCode
        , rtu_addr AS rtuAddr
        , rtu_install_date AS rtuInstallDate
        , rtu_install_person AS rtuInstallPerson
        , rtu_contact AS rtuContact
        , comm_card AS commCard
        , remote_model AS remoteModel
        , remote_memo AS remoteMemo
        , comm_isp AS commIsp
        , meter_memo AS memo
        FROM bw.bw_meter
        <where>
            <if test="meterId != null">
                (meter_id LIKE #{meterId} OR ext_id LIKE #{meterId})
            </if>
            <if test="userCode != null">
                AND user_code LIKE #{userCode}
            </if>
            <if test="meterName != null">
                AND meter_name LIKE #{meterName}
            </if>
            <if test="extId != null">
                AND ext_id LIKE #{extId}
            </if>
            <if test="dmaId != null">
                AND dma_id LIKE #{dmaId}
            </if>
            <if test="typeId != null">
                AND meter_type_id = #{typeId}
            </if>
            <if test="installDate1 != null">
                AND install_date &gt;= #{installDate1}
            </if>
            <if test="installDate2 != null">
                AND install_date &lt; #{installDate2}
            </if>
            <if test="location != null">
                AND meter_location LIKE #{location}
            </if>
            <if test="meterBrandId != null">
                AND meter_brand_id LIKE #{meterBrandId}
            </if>
            <if test="remoteBrandId != null">
                AND remote_brand_id LIKE #{remoteBrandId}
            </if>
            <if test="firmId != null">
                AND firm_id LIKE #{firmId}
            </if>
            <if test='meterIdList != null'>
                AND meter_id IN
                <foreach collection='meterIdList' item='mid' open='(' separator=',' close=')'>
                    #{mid}
                </foreach>
            </if>
        </where>
        ORDER BY meter_id
        LIMIT 0, 1500
        </script>""")
    fun selectMeter(mp: MeterParam): List<ZoneMeter>

    @Select("""<script>
        SELECT meter_id as id, user_code AS userCode,
        meter_code AS meterCode,
        meter_order AS meterOrder,
        ext_id AS extId,
        meter_name as name, meter_location as location, install_date as installDate,
        meter_pulse AS meterPulse,
        q1, q2, q3, q4, q1r, q2r, q3r, q4r,
        meter_size as size, meter_model as model, meter_type_id as typeId, is_charge as chargable,
        in_out AS inOutput,
        create_by as createBy, create_date as createDate, update_by as updateBy, update_date as updateDate,
        dma_id as dmaId,
        meter_brand_id as meterBrandId,
        remote_brand_id as remoteBrandId,
        rtu_id AS rtuId,
        pressure_range AS pressureRange,
        pressure_max_limit AS pressureMaxLimit,
        pressure_min_limit AS pressureMinLimit,
        power_type AS powerType,
        meter_status AS meterStatus,
        admin_mobile AS adminMobile,
        last_calib AS lastCalib,
        ST_AsText(meter_loc) AS meterLoc,
        firm_id AS firmId
        , water_price AS waterPrice
        , service_area AS serviceArea
        , service_population AS servicePopulation
        , contact_number AS contactNumber
        , rtu_code AS rtuCode
        , rtu_addr AS rtuAddr
        , rtu_install_date AS rtuInstallDate
        , rtu_install_person AS rtuInstallPerson
        , rtu_contact AS rtuContact
        , comm_card AS commCard
        , remote_model AS remoteModel
        , remote_memo AS remoteMemo
        , comm_isp AS commIsp
        , meter_memo AS memo
        FROM bw.bw_meter
        WHERE MATCH(meter_id, user_code, meter_code, meter_name, meter_location, `meter_memo`, `remote_memo`, `rtu_addr`, `rtu_install_person`, `ext_id`)
        AGAINST(#{keywords} IN BOOLEAN MODE)
        <when test='firmId != null'>
            AND firm_id LIKE #{firmId}
        </when>
        </script>""")
    fun selectMeterText(mp: MeterParam): List<ZoneMeter>

    /**
     * The meter where type = 'CHECK'.
     *
     * @return
     */
    @Select("""<script>
        SELECT meter_id as id, user_code AS userCode,
        meter_code AS meterCode,
        meter_order AS meterOrder,
        ext_id AS extId,
        meter_name as name, meter_location as location, install_date as installDate,
        meter_pulse AS meterPulse,
        q1, q2, q3, q4, q1r, q2r, q3r, q4r,
        meter_size as size, meter_model as model, meter_type_id as typeId, is_charge as chargable,
        in_out AS inOutput,
        create_by as createBy, create_date as createDate, update_by as updateBy, update_date as updateDate,
        dma_id as dmaId,
        meter_brand_id as meterBrandId,
        remote_brand_id as remoteBrandId,
        rtu_id AS rtuId,
        pressure_range AS pressureRange,
        pressure_max_limit AS pressureMaxLimit,
        pressure_min_limit AS pressureMinLimit,
        power_type AS powerType,
        meter_status AS meterStatus,
        admin_mobile AS adminMobile,
        last_calib AS lastCalib,
        ST_AsText(meter_loc) AS meterLoc,
        firm_id AS firmId
        , water_price AS waterPrice
        , service_area AS serviceArea
        , service_population AS servicePopulation
        , contact_number AS contactNumber
        , rtu_code AS rtuCode
        , rtu_addr AS rtuAddr
        , rtu_install_date AS rtuInstallDate
        , rtu_install_person AS rtuInstallPerson
        , rtu_contact AS rtuContact
        , comm_card AS commCard
        , remote_model AS remoteModel
        , remote_memo AS remoteMemo
        , comm_isp AS commIsp
        , meter_memo AS memo
        FROM bw.bw_meter
        <where>
            meter_type_id = 'CHECK'
            <if test="firmId != null">
                AND firm_id LIKE #{firmId}
            </if>
        </where>
        ORDER BY meter_id
        LIMIT 0, 1500
        </script>""")
    fun selectParentMeter(map: Map<String, Any>): List<ZoneMeter>

    /**
     * The meter with type = 'CHECK' for specified DMA.
     *
     * @param dmaId
     * @return
     */
    @Select("""<script>
        SELECT meter_id as id, user_code AS userCode,
        meter_code AS meterCode,
        meter_order AS meterOrder,
        ext_id AS extId,
        meter_name as name, meter_location as location, install_date as installDate,
        meter_pulse AS meterPulse,
        q1, q2, q3, q4, q1r, q2r, q3r, q4r,
        meter_size as size, meter_model as model, meter_type_id as typeId, is_charge as chargable,
        in_out AS inOutput,
        create_by as createBy, create_date as createDate, update_by as updateBy, update_date as updateDate,
        dma_id as dmaId,
        meter_brand_id as meterBrandId,
        remote_brand_id as remoteBrandId,
        rtu_id AS rtuId,
        pressure_range AS pressureRange,
        pressure_max_limit AS pressureMaxLimit,
        pressure_min_limit AS pressureMinLimit,
        power_type AS powerType,
        meter_status AS meterStatus,
        admin_mobile AS adminMobile,
        last_calib AS lastCalib,
        ST_AsText(meter_loc) AS meterLoc,
        firm_id AS firmId
        , water_price AS waterPrice
        , service_area AS serviceArea
        , service_population AS servicePopulation
        , contact_number AS contactNumber
        , rtu_code AS rtuCode
        , rtu_addr AS rtuAddr
        , rtu_install_date AS rtuInstallDate
        , rtu_install_person AS rtuInstallPerson
        , rtu_contact AS rtuContact
        , comm_card AS commCard
        , remote_model AS remoteModel
        , remote_memo AS remoteMemo
        , comm_isp AS commIsp
        , meter_memo AS memo
        FROM bw.bw_meter
        WHERE meter_type_id = 'CHECK'
        AND dma_id = #{dmaId}
        ORDER BY meter_id
        LIMIT 0, 1500
        </script>""")
    fun selectParentMeterByDmaId(dmaId: String): List<ZoneMeter>

    @Select("""<script>
        SELECT
        d.dma_id as id,
        d.dma_name as name,
        d.dma_location as location,
        d.dma_r AS r,
        d.dma_count as count,
        d.user_id as userId,
        d.firm_id as firmId
        , d.sum_user AS sumUser
        , d.dma_type AS dmaType
        , d.legal_lnf AS legalLnf
        , ST_AsText(dl.dma_loc) AS dmaLoc
        , ST_AsText(dl.dma_region) AS dmaRegion
        FROM bw.bw_dma d LEFT JOIN bw.bw_dma_loc dl ON d.dma_id = dl.dma_id
        <where>
            <if test='keywords != null'>
                MATCH (d.dma_id, d.dma_name, d.dma_location)
                AGAINST (#{keywords} IN BOOLEAN MODE)
            </if>
            <if test="firmId != null">
                AND d.firm_id LIKE #{firmId,jdbcType=VARCHAR}
            </if>
            <if test="dmaId != null">
                AND d.dma_id LIKE #{dmaId}
            </if>
        </where>
        </script>""")
    fun selectDma(@Param("firmId") firmId: String?, @Param("dmaId") dmaId: String?,
                  @Param("keywords") keywords: String?): List<BwDma>

    @Select("""<script>
        SELECT
        d.dma_id as id,
        d.dma_name as name,
        d.dma_location as location,
        d.dma_r AS r,
        d.dma_count as count,
        d.user_id as userId,
        d.firm_id as firmId
        , d.sum_user AS sumUser
        , d.dma_type AS dmaType
        , d.legal_lnf AS legalLnf
        , ST_AsText(dl.dma_loc) AS dmaLoc
        , ST_AsText(dl.dma_region) AS dmaRegion
        FROM bw.bw_dma d LEFT JOIN bw.bw_dma_loc dl ON d.dma_id = dl.dma_id
        WHERE d.dma_id = #{dmaId}
        </script>""")
    fun selectDmaById(@Param("dmaId") dmaId: String): BwDma

    @Insert("""
        INSERT INTO bw.bw_dma (dma_id, dma_name, dma_location, dma_r, dma_count, user_id, firm_id
        , sum_user
        , dma_type
        , legal_lnf
        , create_by
        , create_date)
        VALUES(#{id}, #{name}, #{location}, #{r}, #{count}, #{userId}, #{firmId}
        , #{sumUser}
        , #{dmaType}
        , #{legalLnf}
        , #{createBy}
        , #{createDate})
        """)
    fun insertDma(dma: BwDma): Int

    @Update("""<script>
        UPDATE bw.bw_dma
        <set>
            dma_name = #{name},
            dma_location = #{location},
            dma_r = #{r},
            dma_count = #{count},
            user_id = #{userId},
            firm_id = #{firmId}
            , sum_user = #{sumUser}
            , dma_type = #{dmaType}
            , legal_lnf = #{legalLnf}
            ,
            
            <if test="updateBy != null">
                update_by = #{updateBy},
            </if>
            <if test="updateDate != null">
                update_date = #{updateDate},
            </if>

            <if test="firmId != null">
                firm_id = #{firmId}
            </if>
        </set>
        WHERE dma_id = #{id}
        </script>""")
    fun updateDma(dma: BwDma): Int

    @Update("""
        UPDATE bw.bw_dma SET
        user_id = NULL
        WHERE user_id = #{id}
        """)
    fun detachDmaUser(user: BwUser): Int

    /**
     * Attach the user to the DMA list.
     *
     * @param map
     * - userId, dmaIds - can be like "'DMA01', 'DMA02'".
     */
    @Update("""<script>
        UPDATE bw.bw_dma SET
        user_id = #{id}
        WHERE dma_id in (
            <foreach collection='dmaIdList' item='dmaId' index='idx' open='(' separator=',' close=')'>
                #{dmaId}
            </foreach>
        )
        <if test='firmId != null'>
            AND firm_id LIKE #{firmId}
        </if>
        </script>""")
    fun attachDmaUser(user: BwUser): Int

    @Delete("""<script>
        DELETE from bw.bw_dma
        WHERE dma_id = #{dmaId}
        <if test='firmId != null'>
            AND firm_id LIKE #{firmId}
        </if>
        </script>""")
    fun deleteDma(@Param("dmaId") dmaId: String, @Param("firmId") firmId: String?): Int

    /**
     * <pre>
     * <parameterMap type="map" id="linkMeterDmaMap">
     * <parameter property="dmaId"></parameter>
     * <parameter property="meterIds"></parameter>
    </parameterMap> *
    </pre> *
     */
    @Update("""<script>
        UPDATE bw.bw_meter
        SET dma_id = #{dmaId}
        WHERE meter_id in
        <foreach collection='meterIdList' item='meterId' index='idx' open='(' separator=',' close=')'>
            #{meterId}
        </foreach>
        </script>""")
    fun linkMeterDma(dmaId: String, meterIdList: List<String>): Int

    @Update("""<script>
        UPDATE bw.bw_meter
        SET dma_id = null
        WHERE meter_id IN
        <foreach collection='meterIdList' item='meterId' open='(' separator=',' close=')'>
            #{meterId}
        </foreach>
        and dma_id = #{dmaId}
        </script>""")
    fun detachMeterDma(dmaId: String, meterIdList: List<String>): Int

}